﻿using QD.SQLHelper;
using QD.Tools;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace QD.DbManager
{
    public partial class DbTableSizeMag : DbBasic
    {
        private string databases;
        EFDbContext dbContext;
        LogonConfig logonConfig;

        public DbTableSizeMag(LogonConfig logonConfig, string databases)
        {
            InitializeComponent();

            this.databases = databases;
            this.logonConfig = logonConfig;

            DataGridViewHelper.InitGrid(this.grid);
            this.grid.RowTemplate.ContextMenuStrip = this.contextMenuStrip1;
        }

        private void DbTableSizeMag_Load(object sender, EventArgs e)
        {
            dbContext = new EFDbContext(logonConfig.GetConnectionStrings(databases));

            this.toolStripStatusLabel1.Text = "数据加载中，请稍候……";
            //Thread t = new Thread(new ThreadStart(Grid_DataBind)); -- 多线程grid没有纵向滚动条
            //t.Start();
            Grid_DataBind();
            this.toolStripStatusLabel1.Text = "数据加载完成！";
        }

        private void Grid_DataBind()
        {
            string sql = @"if exists(select * from sys.procedures where name='get_tableinfo')
                            begin
                            drop procedure get_tableinfo
                            end

                            go

                            CREATE PROCEDURE get_tableinfo AS
                            begin
	                            if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tablespaceinfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	                            begin
		                            create table tablespaceinfo
		                            (
			                            tablename varchar(50) ,
			                            rows int , reserved varchar(20) ,
			                            data varchar(20),
			                            index_size varchar(20),
			                            unused varchar(20)
		                            )
	                            end

	                            delete from tablespaceinfo
	                            declare @tablename varchar(255)
	                            declare @cmdsql varchar(500)

	                            DECLARE Info_cursor CURSOR FOR
	                            select o.name
	                            from dbo.sysobjects o where OBJECTPROPERTY(o.id, N'IsTable') = 1
	                            and o.name not like N'#%%' order by o.name

	                            OPEN Info_cursor
	                            FETCH NEXT FROM Info_cursor INTO @tablename

	                            WHILE @@FETCH_STATUS = 0
	                            BEGIN
		                            if exists (select * from dbo.sysobjects where id = object_id(@tablename) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
		                            execute sp_executesql
		                            N'insert into tablespaceinfo　exec sp_spaceused @tbname',
		                            N'@tbname varchar(255)',
		                            @tbname = @tablename

		                            FETCH NEXT FROM Info_cursor	INTO @tablename
	                            END

	                            CLOSE Info_cursor
	                            DEALLOCATE Info_cursor
                            end
                            go

                            exec get_tableinfo
                            go

                            select tablename,rows,
                            cast(left(ltrim(rtrim(reserved)),len(ltrim(rtrim(reserved)))-2) as int)*1.0/1024 as [reserved (MB)],
                            cast(left(ltrim(rtrim(data)),len(ltrim(rtrim(data)))-2) as int)*1.0/1024 as [data (MB)], 
                            cast(left(ltrim(rtrim(index_size)),len(ltrim(rtrim(index_size)))-2) as int)*1.0/1024 as [index_size (MB)],
                            cast(left(ltrim(rtrim(unused)),len(ltrim(rtrim(unused)))-2) as int)*1.0/1024 as [unused (MB)]
                            from tablespaceinfo";

            DataTable dt = dbContext.Query(sql);
            dt.Columns["reserved (MB)"].ColumnName = "reserved";
            dt.Columns["data (MB)"].ColumnName = "data";
            dt.Columns["index_size (MB)"].ColumnName = "index_size";
            dt.Columns["unused (MB)"].ColumnName = "unused";

            this.reservedtotal.Text = dt.Compute("sum(reserved)", "TRUE").ToString();
            this.datatotal.Text = dt.Compute("sum(data)", "TRUE").ToString();
            this.index_sizetotal.Text = dt.Compute("sum(index_size)", "TRUE").ToString();
            this.unusedtotal.Text = dt.Compute("sum(unused)", "TRUE").ToString();

            this.grid.DataSource = dt;
        }

        private void 复制单元格ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.grid.CopyFirstSelectedCellValue();
        }

        private void 复制所有选中单元格ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.grid.CopySelectedCellsValue();
        }
    }
}
